# Row Oriented Security Using Triggers

## R Glen Cooper

### Overview

This article introduces a simple model for row-oriented security. It is based on a small number of security tables and a few triggers. The model is independent of SQL Server's own security, and may be used in other database products with minimal change.
The basic premise of this model is that some user table acts as a gateway to all other tables. Row-oriented security uses a single table to list which rows of the gateway table may be accessed by a given user. Such users may access related rows in other tables if access has been granted to the corresponding gateway rows.
It also assumes that users will access the database through approved applications only. That's because the model puts a tiny part of the security burden on the front-end applications instead of the database server (where most of the security processing occurs). In particular, those apps can implement additional row-oriented and column-oriented security without additional programming.
This model has been successfully used by a large Medical faculty for several years.

### Example

To make things really simple, suppose our database contains exactly one table Employee for which, say, an HR application has been written:
Fig. 1
In that application, we would like each user to be restricted to various rows depending on the roles that user has been assigned. We would like those restrictions to be defined in a set of security tables that the application reads at run-time, and which may be modified in real-time by an administrator.
The rows in Employee that each role may access will be defined by filters on that table, such as:
`SELECT EmployeeId AS SelectId FROM Employee WHERE City = 'Vancouver'`
Such filters simply list key values of the gateway table with a fixed name `SelectId`. Each role may have any number of filters assigned to it and each user may have multiple roles. For convenience, the gateway table's key and `SelectId` are INT columns (although that can be easily changed).
We also want the rows that any role (and user) can see to accurately reflect the current state of the data (an employee may move at any time, for example) or the filters currently assigned to it (they may be inserted or deleted at any time).
Furthermore, the app itself should automatically impose additional row- and column-oriented constraints, such as preventing record deletions or hiding certain fields, depending on the restrictions entered into the security tables by the administrator.
The model presented here does that, using special security tables that designate what roles users belong to, what rows each role may access in the gateway table, what kind of editing may be done on those rows, and what columns will be hidden by the apps.

### Functionality

Users, roles and restrictions are defined by an organization using special security tables added to the database.
A row-oriented restriction is nothing more than a filter defining what rows of the gateway table may be accessed by a given role. Such filters may also be defined using additional tables, such as lookup tables.
Users may be assigned any number of roles. They automatically inherit the "sum" of all security restrictions on those roles.
The filtering mechanism for invoking security works dynamically, using triggers that fire whenever the gateway table is modified:

- If any row in the gateway table becomes visible to a security filter (because some underlying data has suddenly changed) then that row is immediately available to any role whose security includes that filter.
- Correspondingly, if any row in the gateway table becomes invisible to a security filter then that row is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

Furthermore, any changes to the set of security filters is immediately witnessed by all users, using triggers that fire whenever one of them is added or deleted:

- If any security filter is added, then each row in the gateway table defined by it is immediately available to any role whose security includes that filter.
- Correspondingly, if any security filter is deleted then each row of the gateway table defined by it is no longer available to any role whose security included that filter (unless some other security filter for that role still allows it).

### Implementation

The entire system is written using triggers and stored procedures. So it is basically independent of any desktop or web application, which need only include some standard, front-end security code to use it. That's why only "approved" applications are allowed to access the database. Of course, one may bypass the security model by simply ignoring the security tables. That allows developers to test applications that are security-free, before imposing user restrictions prior to deployment. Because a single relationship connects the security tables to the gateway table, adding security to any approved app simply involves "fooling" the app by replacing the gateway table with a recordset filtered by security. In that way, legacy apps may have row-oriented security bolted onto them fairly quickly.
For convenience, a VB.NET security manager interactively defines users, roles and restrictions. Any changes to security are witnessed by users when they log in through an approved app.
This system is ideal for a Citrix server farm (where it was originally developed), where only approved applications access data and the underlying recordsets and code are hidden from the user.

### Security Model

Fig. 2
The lkpUser and lkpRole tables define users and roles, connected by the tblUserRole table.
The lkpView table defines various "views" (not SQL views) a role might use to access data from the same table under various contexts. To keep things simple for this article, there's only one view and everybody has it.
The key security table is tblRoleAccessSelect. For each record, the foreign key `SelectId` points to a record in the gateway table Employee for a given Role and View. Approved applications no longer see Employee (which has been renamed and hidden), but rather a subset of it silently filtered by the security table tblRoleAccessSelect. Users will not be aware of this restriction except that rows in the filtered table will appear/disappear whenever changes are made to user data or the tblRoleAccessSelectFilter table. That's because the key security table is dynamically populated by whatever filters have been added or deleted in the tblRoleAccessSelectFilter table.
The tblRoleAccessSelectFilter table assigns any number of filters to each role and view. Any such filter is simply a query with one column `SelectId` that lists what rows are available in the Employee table (which is our gateway table for his article).
The main problem is to dynamically change tblRoleAccessSelect whenever user data changes (which will affect the output of those filters), or when changes are made to the tblRoleAccessSelectFilter table. Triggers and stored procedures do this automatically.
The lkpAccessRow and lkpAccessColumn tables define additional row- and column- oriented restrictions that each app could automatically impose using front-end code provided by the model:
Fig. 3
For rows, you may allow/disallow editing, adding, or deleting. For columns, you may allow hiding or locking (or specify full control). A given user may have several roles and views, so the highest priority wins in each case.
The tblRoleAccessRow table assigns available row-level restrictions to various roles and views.
The tblRoleAccessColumn table assigns available column-level restrictions to various roles and views.
It's these tables that the HR application's front-end code uses to decide how to edit rows that have been made available to it, and what columns it can see. This code may be further customized if you define more restrictions in the lkpAccessRow and lkpAccessColumn tables.

### How The Model Works (server side)

The key security table tblRoleAccessSelect points to those records in the gateway table Employee that may be seen by any given role and view.
If a row is updated/inserted into the gateway table, the following trigger passes its key to the stored proc `sqRoleAccessSelectSync`.
This proc insures that the correct rows in the key security table are inserted or deleted, to re-synchronize with all of the filters assigned to each role and view:

    ALTER TRIGGER [dbo].[TR_Employee_Update_Insert] ON [dbo].[Employee]
    AFTER UPDATE, INSERT
    AS

    -- No counting
    SET NOCOUNT ON

    -- Declarations
    DECLARE @MyId INT

    -- If exactly one record updated/inserted
    IF (SELECT COUNT(*) FROM INSERTED) = 1
    	BEGIN
    	-- Determine key of record
    	SELECT @MyId = (SELECT EmployeeId FROM INSERTED)
    	-- Synchronize security table for record updated/inserted
    	EXECUTE sqRoleAccessSelectSync @MyId
    	END

In other words, when user data changes, the key security table will always be in sync with the filters initially used to populate it.
The sole task of the stored proc `sqRoleAccessSelectSync` is to insure that for each role, view and filter in the table tblRoleAccessSelectFilter, the row (role, view, Id) belongs to the table tblRoleAccessSelect if and only if Id is selected by at least one of the filters in that table with the same role and view:

    ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync]
    	(
    	@Id INT
    	)
     AS

    /*
    For each Role, View, and Filter in tblRoleAccessSelectFilter,
    check if @Id belongs to that filter's result set.

    Insure that (Role, View, @Id) belongs to RoleAccessSelect if, and only if,
    @Id belongs to at least one of the result sets for that Role and View.

    Note that each Role, View combination may be associated with multiple Filters.

    So if @Id belongs to the result set of one filter, care must be taken to avoid
    deleting the corresponding row in RoleAccessSelect if @Id does not belong to the
    result set of another filter with the same Role and View.

    Avoid this by ordering the Filters by Role and View and using a flag to check
    for an inserted @Id for all Filters of a given Role and View.

    Reset that flag when a new Role and View combination appears.
    */

    -- Declarations
    DECLARE @RoleIdCurrent	INT	-- current Role for all Filters being checked
    DECLARE @ViewIdCurrent	INT	-- current View for all Filters being checked
    DECLARE @ynInserted	INT	-- inserted flag for all Filters being checked with given Role, View

    -- Declarations for cursor
    DECLARE @RoleId		INT
    DECLARE @ViewId		INT
    DECLARE @FilterName	NVARCHAR(256)

    -- Cursor for tblRoleAccessSelectFilter listing Roles, Views, and Filters (ordered by Role, View)
    DECLARE curTable CURSOR
    FOR
    SELECT RoleId, ViewId, FilterName FROM dbo.tblRoleAccessSelectFilter ORDER BY RoleId, ViewId
    FOR READ ONLY

    -- Set inserted flag to off
    SET @ynInserted = 0

    -- Open cursor
    OPEN curTable

    -- Fetch first row
    FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName

    -- Save current Role, View combination for which inserted check will be monitored
    SET @RoleIdCurrent = @RoleId
    SET @ViewIdCurrent = @ViewId

    -- Do this while rows are fetched and inserted @Id does not exist for current Role, View
    -- If inserted @Id exists, reset inserted flag to prevent possible deletion later on for current Role, View
    -- Reset inserted flag to off if new Role, View combination appear
    While @@FETCH_STATUS = 0
    	BEGIN
    	-- Check if @ID is in filter and insert/delete corresponding row into tblRoleAccessSelect if it is/is not
    	-- But only do this if inserted flag is off (otherwise there's nothing more to do for current Row, View)
    	IF @ynInserted = 0
    		EXECUTE sqRoleAccessSelectSync2
    				@Id = @Id,
    				@RoleId = @RoleId,
    				@ViewId = @ViewId,
    				@FilterName = @FilterName,
    				@ynInserted = @ynInserted OUTPUT
    	FETCH NEXT FROM curTable INTO @RoleId, @ViewId, @FilterName
    	-- If Row, View combination changes, reset inserted flag to off and continue
    	IF (@RoleId <> @RoleIdCurrent) OR (@ViewId <> @ViewIdCurrent)
    		BEGIN
    		SET @ynInserted = 0
    		SET @RoleIdCurrent = @RoleId
    		SET @ViewIdCurrent = @ViewId
    		END
    	END

    -- Close cursor
    CLOSE curTable

    -- Deallocate cursor
    DEALLOCATE curTable

It does this by calling the stored proc `sqRoleAccessSelectSync2` which insures that the row `(role, view, Id)` belongs to the table tblRoleAccessSelect if and only if `Id` is selected by the passed filter. However, this proc passes back to its caller a flag indicating whether an insertion occurred (or the row (role, view, Id) was already found to exist) so that the calling program knows when to avoid deleting rows unnecessarily:

    ALTER PROCEDURE [dbo].[sqRoleAccessSelectSync2]
    	(
    	@Id		INT,
    	@RoleId		INT,
    	@ViewId		INT,
    	@FilterName	NVARCHAR(256),
    	@ynInserted	INT OUTPUT
    	)
    AS

    /*
    Insure that (RoleId, ViewId, @Id) belongs to RoleAccessSelect
    if @Id belongs to the result set of filter @FilterName (otherwise delete it).

    Note that this call could delete (RoleId, ViewId, @Id) in tblRoleAccessSelect after
    an earlier call inserted it, or discovered it was already inserted, for the same
    RoleId, ViewId.

    So the calling script must insure that this doesn't happen by monitoring the value
    of @ynInserted set by this call.
    */

    BEGIN

    SET NOCOUNT ON

    -- Declarations
    DECLARE @SQLString NVARCHAR(1028)

    -- Set inserted flag off
    SET @ynInserted = 0

    -- Check if @Id belongs to the result set of filter @FilterName
    -- Use temporary table to avoid result sets in caller
    -- Note that SelectId is always a single column in @FilterName, by convention
    SET @SQLString = N'SELECT TOP 1 * INTO #t FROM dbo.' + @FilterName + '  WHERE (SelectId = ' + CAST(@Id AS NVARCHAR(10))  + ')'
    EXEC sp_executesql @SQLString

    -- If it is
    IF (@@ROWCOUNT = 1)
    	BEGIN
    	-- Check if it is in tblRoleAccessSelect with given Role and View
    	SET @SQLString = N'SELECT SelectId FROM tblRoleAccessSelect WHERE SelectId = ' + CAST(@Id AS NVARCHAR(10)) + ' AND RoleId = ' + CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10))
    	EXEC sp_executesql @SQLString
    	-- If it isn't
    	IF (@@ROWCOUNT = 0)
    		BEGIN
    		-- Insert it
    		SET @SQLString = N'INSERT INTO tblRoleAccessSelect VALUES (' +  CAST(@RoleId AS NVARCHAR(10)) + ',' + CAST(@ViewId AS NVARCHAR(10)) + ',' + CAST(@Id AS NVARCHAR(10)) + ')'
    		EXEC sp_executesql @SQLString
    		-- Set inserted flag on (because it has just been inserted)
    		SET @ynInserted = 1
    		END
    	-- If it is
    	ELSE
    		-- Set inserted flag on (because it was already inserted)
    		SET @ynInserted = 1
    	END
    ELSE
    	-- If it isn't, then it can't be for this (or any other) Role, View combination
    	BEGIN
    	-- So delete it for current Role, View combination
    	SET @SQLString = N'DELETE FROM tblRoleAccessSelect WHERE RoleId = ' +  CAST(@RoleId AS NVARCHAR(10)) + ' AND ViewId = ' + CAST(@ViewId AS NVARCHAR(10)) + ' AND SelectId = ' + CAST(@Id AS NVARCHAR(10))
    	EXEC sp_executesql @SQLString
    	END
    END

It should be noted that if a row is deleted from the Employee table, the (cascading delete) relationship connecting it to the key security table takes affect.
A subtle condition on the model is that if a row can/cannot be seen in the gateway table by a given filter, then it remains that way no matter what other other rows are modified. For example, if a filter sees an Employee because they live in Vancouver, that wouldn't change if someone else moves to/from that city. For a bizarre example, a filter that sees everything if and only if there are exactly 100 rows would be invalid. This condition is always satisfied by ordinary filtering for our purposes.
Of course, we must also handle the event where rows have been inserted/deleted in the tblRoleAccessSelectFilter table (updates are not allowed).
For insertions, the following trigger `TR_tblRoleAccessSelectFilter_Insert` on that table is used to populate the key security table with key values for the inserted role and view that aren't already present (to avoid duplicates):

    ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Insert] ON [dbo].[tblRoleAccessSelectFilter]
    FOR INSERT
    AS
    BEGIN

    -- Declarations
    DECLARE @SQLString		NVARCHAR(1028)
    DECLARE @RoleId			INT
    DECLARE @ViewId			INT
    DECLARE @FilterName		NVARCHAR(256)

    -- Do nothing unless INSERTED contains exactly one row
    IF @@ROWCOUNT <> 1
    	RETURN

    -- Avoid extra result sets
    SET NOCOUNT ON

    -- Get RoleId, ViewId, Filter from record that's been INSERTED
    SELECT @RoleId		= (SELECT RoleId FROM INSERTED)
    SELECT @ViewId		= (SELECT ViewId FROM INSERTED)
    SELECT @FilterName	= (SELECT FilterName FROM INSERTED)

    -- Insert into tblRoleAccessSelect
    -- Note that @FilterName must name an existing filter (otherwise insertion will fail)
    SET @SQLString =
    N'INSERT INTO tblRoleAccessSelect(RoleId,ViewId,SelectId) SELECT D1.RoleId,D1.ViewId,D1.SelectId FROM
    (SELECT ' + CAST(@RoleId as NVARCHAR(10)) + ' AS RoleId,' + CAST(@ViewId as NVARCHAR(10)) + ' AS ViewId,' + @FilterName + '.SelectId FROM ' + @FilterName + ') D1 LEFT OUTER JOIN tblRoleAccessSelect ON ' + 'D1.RoleId = tblRoleAccessSelect.RoleId AND ' + 'D1.ViewId = tblRoleAccessSelect.ViewId AND D1.SelectId = tblRoleAccessSelect.SelectId WHERE tblRoleAccessSelect.SelectId IS NULL'

    EXEC sp_executesql @SQLString

    END

    -- Finalization
    SET NOCOUNT OFF

Deletions are a little more complicated:

    ALTER TRIGGER [dbo].[TR_tblRoleAccessSelectFilter_Delete] ON [dbo].[tblRoleAccessSelectFilter]
    FOR DELETE
    AS
    BEGIN

    -- Declarations
    DECLARE @SQLString		NVARCHAR(2048)	-- outer SQL string
    DECLARE @SQLString1		NVARCHAR(2048)	-- inner SQL string consisting of UNIONs (variable number)
    DECLARE @RoleId			INT
    DECLARE @ViewId			INT
    DECLARE @FilterName		NVARCHAR(256)
    DECLARE @RoleIdSave		INT
    DECLARE @ViewIdSave		INT
    DECLARE @FilterNameSave		NVARCHAR(256)

    -- Do nothing unless DELETED contains exactly one row
    IF (SELECT COUNT(*) FROM DELETED) <> 1
    	RETURN

    -- Avoid extra result sets
    SET NOCOUNT ON

    -- Get @RoleId, @ViewId, @Filter from DELETED record
    SELECT @RoleId		= (SELECT RoleId FROM DELETED)
    SELECT @ViewId		= (SELECT ViewId FROM DELETED)
    SELECT @FilterName	= (SELECT FilterName FROM DELETED)

    -- Save Role, View and Filter for use in building SQL outer string
    SET @RoleIdSave		= @RoleId
    SET @ViewIdSave		= @ViewId
    SET @FilterNameSave	= @FilterName

    -- Start building inner SQL string
    SET @SQLString1 = 'SELECT SelectId FROM ' + @FilterName

    -- Cursor for tblRoleAccessSelectFilter listing Filters
    -- with the same Roles, Views as DELETED record
    DECLARE curTable CURSOR
    FOR
    SELECT FilterName FROM dbo.tblRoleAccessSelectFilter
    WHERE
    RoleId = @RoleId AND
    ViewId = @ViewId
    FOR READ ONLY

    -- Open cursor
    OPEN curTable

    -- Fetch next row (note that deleted row does not belong to cursor)
    FETCH NEXT FROM curTable INTO @FilterName

    -- Continue building SQL inner string
    While @@FETCH_STATUS = 0
    	BEGIN
    	SET @SQLString1 = @SQLString1 + ' UNION ALL SELECT SelectId FROM ' + @FilterName
    	FETCH NEXT FROM curTable INTO @FilterName
    	END

    -- Delete from tblRoleAccessSelect (explanation below)
    SET @SQLString =
    N'DELETE FROM tblRoleAccessSelect WHERE
    RoleId = ' + CAST(@RoleIdSave AS NVARCHAR(10)) + ' AND
    ViewId = ' + CAST(@ViewIdSave AS NVARCHAR(10)) + ' AND
    SelectId IN
    (
    SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
    INNER JOIN ' +
    @FilterNameSave +
    ' ON
    tblRoleAccessSelect.SelectId = ' + @FilterNameSave + '.SelectId
    INNER JOIN
    (
    SELECT SelectId FROM
    (' +
    @SQLString1 +
    ') d1
    GROUP BY SelectId
    HAVING COUNT(*) = 1
    ) d2
    ON
    tblRoleAccessSelect.SelectId = d2.SelectId
    )'

    /*
    Sample deletion for filter ftr1 but where ftr2 has same Role, View.
    Avoid deleting any SelectId belonging to ftr1 if it also belongs to ftr2.

    DELETE
    FROM tblRoleAccessSelect WHERE
    RoleId = 1 AND
    ViewId = 1 AND
    SelectId IN
    (
    -- Find the SelectId values in tblRoleAccessSelect belonging to ftr1
    SELECT tblRoleAccessSelect.SelectId FROM tblRoleAccessSelect
    INNER JOIN
    ftr1
    ON
    tblRoleAccessSelect.SelectId = ftr1.SelectId
    -- But make sure they don't also belong to ftr2
    INNER JOIN
    (
    -- UNION of SelectId for two filters without duplication (but where COUNT = 1).
    -- This guarantees that each SelectId belongs to exactly one filter.
    SELECT SelectId FROM
    (
    -- UNION of SelectId for two filters (with duplication)
    -- This is the inner SQL string
    SELECT SelectId FROM ftr1
    UNION ALL
    SELECT SelectId FROM ftr2
    ) d1
    GROUP BY SelectId
    HAVING COUNT(*) = 1
    ) d2
    ON
    tblRoleAccessSelect.SelectId = d2.SelectId
    )
    */

    EXEC sp_executesql @SQLString

    -- Close cursor
    CLOSE curTable

    -- Deallocate cursor
    DEALLOCATE curTable

    -- Finalization
    SET NOCOUNT OFF

    END

Here we have to be careful not to delete a row in the key security table if some other filter with the same role and view still wants it present.

### How The Model Works (client side)

Each client application runs a hidden piece of security code when any form is opened, which automatically provides additional row- or column- oriented security. For example, the following snippet locks or hides any control referenced by the tblRoleAccessColumn table:

    Dim reader As SqlDataReader = cmd.ExecuteReader()
    While reader.Read() ' Loop through constraints for current test
        MySecurity(0) = reader.Item(0).ToString ' ColumnName
        MySecurity(1) = reader.Item(1).ToString ' AccessColumnName
        For Each ctrl As Control In Me.Controls ' Loop through all controls
            ' If this is one of the following controls
            If (TypeOf ctrl Is TextBox) Or _
            (TypeOf ctrl Is Label) Or _
            (TypeOf ctrl Is Button) Or _
            (TypeOf ctrl Is ListBox) Or _
            (TypeOf ctrl Is ListView) Then
                If MySecurity(0) = ctrl.Name Then   ' Referenced by current constraint
                    ctrl.Enabled = True             ' Enable it and make it visible
                    ctrl.Visible = True
                    If MySecurity(1) = "Lock" Then  ' Lock it if required
                        ctrl.Enabled = False
                    End If
                    If MySecurity(1) = "Hide" Then  ' Hide it if required
                        ctrl.Visible = False
                    End If
                    If MySecurity(1) = "Full" Then  ' Full access if required
                        ctrl.Enabled = True
                        ctrl.Visible = True
                    End If
                End If
            End If
        Next
    End While
    reader.Close()

Strictly speaking, the ColumnName column in the tblRoleAccessColumn table is the form's control name, not the column name of the underlying table. Of course, an app and form name should also be part of the tblRoleAccessColumn table's definition (for greater flexibility) but this is omitted for simplicity.
A Visual Basic 2005 Express source and executable package for maintaining the model (along with a front-end application and sample database) may be found here.

### Example

In this example there are two Users, two Roles, and one View. The Employee table has 100 randomly generated records.
User1 has Role1 while User2 has Role1 and Role2 (all views are View1 so it will be ignored in what follows).
Role1 has filter ftr1, which lists those Employees whose names start with A or B:

    CREATE VIEW dbo.ftr1
    AS
    SELECT     EmployeeId AS SelectId
    FROM       dbo.Employee
    WHERE     (SUBSTRING(LastName, 1, 1) IN ('A', 'B'))

It also has ftr2 (Employees whose names start with B or C).
Role2 has filters ftr3 (Employees whose names start with C or D) and ftr4 (Employees whose names start with E).
When User2 and View1 are selected, clicking RoleAccessSelect yields 42 (Role, View, Select) combinations:
Fig. 4
A given Employee may appear multiple times if the selected User belongs to multiple Roles (in this case 28 Employees appear 42 times with different Roles and Views).
The SQL query generated for this choice of User, Role and View is:

    SELECT DISTINCT
    lkpRole.RoleName,
    lkpView.ViewName,
    Employee.LastName,
    tblRoleAccessSelect.SelectId FROM
    Employee INNER JOIN
    tblRoleAccessSelect ON
    Employee.EmployeeId = tblRoleAccessSelect.SelectId INNER JOIN
    lkpRole ON
    tblRoleAccessSelect.RoleId = lkpRole.RoleId INNER JOIN
    tblUserRole ON
    lkpRole.RoleId = tblUserRole.RoleId INNER JOIN
    lkpView ON
    tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN
    lkpUser ON
    tblUserRole.UserId = lkpUser.UserId
    WHERE
    (lkpUser.UserName = 'User2') AND
    (1=1) AND
    (lkpView.ViewName = 'View1')
    ORDER BY
    lkpRole.RoleName,
    lkpView.ViewName,
    Employee.LastName

Meanwhile, on any apps using the standard security snap-in:
Role1 hides the control tbEmail
Role2 hides the controls tbBirthDate and tbSIN and locks the control tbEmail
Any controls that don't exist are simply ignored.
When the application opens, 28 unique records may be seen by User2 and View1 when Employees is clicked:
Fig. 5
The security stored proc `sqMain` generates the SQL that drives this listing:

    SELECT
    EmployeeId,
    LastName,
    FirstName,
    Address,
    City,
    Postal,
    Prov,
    Phone,
    Email,
    BirthDate,
    [SIN]
    FROM
    Employee
    WHERE EmployeeId IN
    (
    SELECT DISTINCT
    tblRoleAccessSelect.SelectId
    FROM
    tblRoleAccessSelect INNER JOIN
    lkpView ON
    tblRoleAccessSelect.ViewId = lkpView.ViewId INNER JOIN
    tblUserRole ON
    tblRoleAccessSelect.RoleId = tblUserRole.RoleId
    WHERE
    UserId = 2 AND
    ViewName = 'View1'
    )
    ORDER BY
    LastName,
    FirstName

After selecting an Employee, clicking the Test Security button will lock the Email control and hide the BirthDate and SIN controls (locking has greater priority than hiding, although this priority can be changed in the tblRoleAccessColumn table).
Of course, this demo app doesn't initially secure anything when it first opens, but it allows us to use the Test Security button to test other User-View combinations without re-starting. In real life, the code behind that button would be run when the form opens. The operator's User and View must be passed to the security stored proc `sqMain`. The application programmer will decide how to determine those parameters so they correspond to what's in the security tables.
Row level editing constraints are not included in this demo.

### Summary

This article has presented a simple model for implementing row-oriented security in SQL Server. Although it puts some of the security burden on the client application (so it's more vulnerable) it has proven useful in situations where access to front-end applications is tightly controlled. Also, it's easy to deploy and surprisingly efficient (even when the key security table is large). Furthermore, legacy applications may use it with a simple addition to its front-end code.
For the mathematically inclined, the key security table is really the union of a dynamic set of unary relations (i.e. sets) that is automatically maintained by triggers on the data upon which that union depends.